import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class testing extends Assignment2
{
	public static void main(String[] args) {
		Assignment2 k = new Assignment2 (); 
		k.connectDB("localhost", "g9cuiyi", "cuiyi");
//			System.out.println(k.chgDept("sadas", "csc"));
//			System.out.println(k.insertStudent(23432, "Yuan", "LEE", 12, "M", "CSC", 54));
//			System.out.println(k.connection);
			
		try{	
			Statement stmt = k.connection.createStatement();
			
			// delete everything first
			
			System.out.println("Droping all tables.\n");
			stmt.executeUpdate("DROP TABLE department CASCADE;");
			stmt.executeUpdate("DROP TABLE student CASCADE;");
			stmt.executeUpdate("DROP TABLE instructor CASCADE;");
			stmt.executeUpdate("DROP TABLE course CASCADE;");
			stmt.executeUpdate("DROP TABLE courseSection CASCADE;");
			stmt.executeUpdate("DROP TABLE studentCourse CASCADE;");
			stmt.executeUpdate("DROP TABLE prerequisites CASCADE;");
			
			
			
			
			System.out.println("\nCreating table department.");
			stmt.executeUpdate("CREATE TABLE department (        dcode       CHAR(3) PRIMARY KEY,        dname       VARCHAR(20) NOT NULL);");
			System.out.println("Creating table studnet");
			stmt.executeUpdate("CREATE TABLE student (" + 
        "sid         INTEGER PRIMARY KEY," +
        "slastname   CHAR(20) NOT NULL," +
        "sfirstname  CHAR(20) NOT NULL," +
        "sex	    CHAR(1)  NOT NULL," +
        "age 	    INTEGER  NOT NULL," +
        "dcode       CHAR(3)  NOT NULL," +
        "yearofstudy INTEGER  NOT NULL," +
        "FOREIGN KEY (dcode) REFERENCES department(dcode) ON DELETE RESTRICT);");
			System.out.println("Creating table instructor");
			stmt.executeUpdate("CREATE TABLE instructor (			        iid         INTEGER PRIMARY KEY,			        ilastname   CHAR(20) NOT NULL,			        ifirstname  CHAR(20) NOT NULL,			        idegree	    CHAR (5) NOT NULL,			        dcode	    CHAR(3)  NOT NULL,			        FOREIGN KEY (dcode) REFERENCES department(dcode) ON DELETE RESTRICT);");
			System.out.println("Creating table course");
			stmt.executeUpdate("CREATE TABLE course (        cid         INTEGER,        dcode	    CHAR(3) REFERENCES DEPARTMENT(dcode) ON DELETE RESTRICT,        cname	    CHAR(20) NOT NULL,        PRIMARY KEY (cid, dcode));");
			System.out.println("Creating table courseSection");
			stmt.executeUpdate("CREATE TABLE courseSection (        csid        INTEGER PRIMARY KEY, cid         INTEGER NOT NULL,        dcode       CHAR(3) NOT NULL,        year	    INTEGER NOT NULL,        semester    INTEGER NOT NULL, section	    CHAR(5) NOT NULL,        iid         INTEGER REFERENCES instructor(iid),        FOREIGN KEY (cid, dcode) REFERENCES course(cid, dcode),  UNIQUE (cid, dcode, year, semester, section));");
			System.out.println("Creating table studentCourse");
			stmt.executeUpdate("CREATE TABLE studentCourse (        sid         INTEGER REFERENCES student(sid),        csid	    INTEGER REFERENCES courseSection(csid),        grade	    INTEGER NOT NULL DEFAULT -1,        PRIMARY KEY (sid, csid));");
			System.out.println("Creating table prerequisites");
			stmt.executeUpdate("CREATE TABLE prerequisites (        cid	    INTEGER  NOT NULL,        dcode	    CHAR (3) NOT NULL,        pcid	    INTEGER  NOT NULL,        pdcode      CHAR(3)  NOT NULL,        FOREIGN KEY (cid, dcode) REFERENCES course(cid, dcode),        FOREIGN KEY (pcid, pdcode) REFERENCES course(cid, dcode),        PRIMARY KEY (cid, dcode,pcid, pdcode));");

			
			//			stmt.executeUpdate("CREATE TABLE View4(year INTEGER NOT NULL,enrollment	INTEGER NOTNULL);");
//			stmt.executeUpdate("CREATE TABLE Query4(year INTEGER NOT NULL,enrollment INTEGER NOT NULL);");
//			stmt.executeUpdate("CREATE TABLE Query5(cname CHAR(20) NOT NULL);CREATE TABLE Query6(fname CHAR(20) NOT NULL,lname CHAR(20) NOT NULL,"
//					+ "cname VARCHAR(20) NOT NULL,year INTEGER NOT NULL, semester INTEGER NOT NULL);");
//			stmt.executeUpdate("CREATE TABLE View7(csid	INTEGER NOT NULL,avgmark INTEGER NOT NULL);");
//			stmt.executeUpdate("CREATE TABLE Query7(cname VARCHAR(20) NOT NULL,semester	INTEGER NOT NULL,year INTEGER NOT NULL avgmark INTEGER NOT NULL);");
			
			System.out.println("Inserting values to tables.");
			stmt.executeUpdate("INSERT INTO department VALUES ('csc', 'computer');");
			stmt.executeUpdate("INSERT INTO department VALUES ('mat', 'math');");
			stmt.executeUpdate("INSERT INTO student VALUES (123, 'last', 'first', 'F', 20, 'csc', 2);");
			stmt.executeUpdate("INSERT INTO student VALUES (321, 'l', 'f', 'M', 21, 'csc', 3);");
			stmt.executeUpdate("INSERT INTO student VALUES (456, 'l2', 'f2', 'F', 23, 'mat', 4);");
			stmt.executeUpdate("INSERT INTO student VALUES (1, 'l1', 'f1', 'F', 23, 'csc', 4);");
			stmt.executeUpdate("INSERT INTO student VALUES (3, 'l3', 'f3', 'F', 23, 'csc', 4);");

			stmt.executeUpdate("INSERT INTO instructor VALUES (111, 'ilast', 'ifirst', 'ddd', 'csc');");
			stmt.executeUpdate("INSERT INTO instructor VALUES (11, 'ilas', 'ifirs', 'dd', 'mat');");
			stmt.executeUpdate("INSERT INTO course VALUES (1, 'csc', 'database2');");
			stmt.executeUpdate("INSERT INTO course VALUES (2, 'csc', 'database1');");
			stmt.executeUpdate("INSERT INTO course VALUES (3, 'mat', 'base');");
			stmt.executeUpdate("INSERT INTO courseSection VALUES (999, 1, 'csc', 2003, 9, 'L1234', 111);");
			stmt.executeUpdate("INSERT INTO courseSection VALUES (9999, 1, 'csc', 2004, 1, 'L124', 111);");
			stmt.executeUpdate("INSERT INTO courseSection VALUES (99, 003, 'mat', 20042, 5, 'L14', 11);");
			stmt.executeUpdate("INSERT INTO studentCourse VALUES (123, 999, 100);");
			stmt.executeUpdate("INSERT INTO studentCourse VALUES (123, 9999, 80);");
			stmt.executeUpdate("INSERT INTO studentCourse VALUES (321, 9999);");
			stmt.executeUpdate("INSERT INTO studentCourse VALUES (1, 999, 90);");
			stmt.executeUpdate("INSERT INTO studentCourse VALUES (3, 999, 90);");
			stmt.executeUpdate("INSERT INTO studentCourse VALUES (321, 999, 100);");
			stmt.executeUpdate("INSERT INTO prerequisites VALUES (001, 'csc', 002, 'csc');");



			
			
			System.out.println("\nTesting function checkDept:  " + k.checkDept("computer"));
			System.out.println("Testing function chgDept:  " + k.chgDept("csc", "computer"));
			System.out.println("Testing function getDcodeByDname:  " + k.getDcodeByDname("computer"));
			//System.out.println("Testing function insertStudent:  " + k.insertStudent(234, "yuan", "Lee", 44, "M", "computer", 4));
			
			
			// should return -1
			// System.out.println(k.getStudentsCount("nsl"));
			// should return 0
			System.out.println("Testing function getStudentCount, expecting 4:   " + k.getStudentsCount("computer"));
			System.out.println("Testing function getStudentCount, expecting 1:   " + k.getStudentsCount("math"));
			System.out.println("Testing function getStudentCount, expecting 0:   " + k.getStudentsCount("com"));
			
			// should be empty string
			System.out.println("Testing function getStudentInfo, expecting empty: " + k.getStudentInfo(234));
			// should work
			System.out.println("Testing function getStudentInfo, expecting a student: " + k.getStudentInfo(123));
			
			// should be false
			System.out.println("Testing function deleteDept, expecting true: " + k.deleteDept("s"));
			//System.out.println("Testing function deleteDept, expecting true: " + k.deleteDept("csc"));
			// should be true
			System.out.println("Testing function deleteDept, expecting true: " + k.deleteDept("mat"));
			
			// should be ''
			System.out.println("Testing function listCourses, expecting empty: " + k.listCourses(13));
			// should be string
			System.out.println("Testing function listCourses, expecting course 001: " + k.listCourses(123));
			
			System.out.println("Testing function updateGrades, expecting true: " + k.updateGrades(999));
			
			System.out.println(k.query7());
			
			System.out.println(k.updateDB());
			k.disconnectDB();
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
			//System.out.println("connection failed.");
			}
	
	}
}
